package com.lagou.jdbc;

import com.lagou.pojo.User;
import org.apache.log4j.Logger;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JdbcTest {

    private Logger logger = Logger.getLogger(JdbcTest.class);

    @Test
    public void select(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 通过驱动管理类获取数据库链接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=utf-8","root","root");
            // 定义sql语句？表示占位符persistence
            String sql = "select * from user where username = ?";
            // 获取预处理statement
            preparedStatement = connection.prepareStatement(sql);
            // 设置参数，第⼀个参数为sql语句中参数的序号(从1开始)，第⼆个参数为设置的参数值
            preparedStatement.setString(1,"kitty");
            // 向数据库发出sql执⾏查询，查询出结果集
            resultSet = preparedStatement.executeQuery();
            // 遍历查询结果集
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String birthday = resultSet.getString("birthday");
                User user = new User();
                user.setId(id);
                user.setUsername(username);
                user.setPassword(password);
                user.setBirthday(birthday);
                System.out.println(user);
            }
        } catch (Exception e) {
            logger.error("jdbc异常", e);
        } finally {
            closResource(resultSet, preparedStatement, connection);
        }
    }

    @Test
    public void insert(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 通过驱动管理类获取数据库链接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=utf-8","root","root");
            // 定义sql语句？表示占位符persistence
            String sql = "insert into user(id,username,password,birthday) values (?,?,?,?)";
            // 获取预处理statement
            preparedStatement = connection.prepareStatement(sql);
            // 设置参数，第⼀个参数为sql语句中参数的序号(从1开始)，第⼆个参数为设置的参数值
            preparedStatement.setInt(1,5);
            preparedStatement.setString(2,"kitty");
            preparedStatement.setString(3,"password");
            preparedStatement.setString(4,"2020-03-01");
            // 向数据库发出sql执⾏查询，查询出结果集
            int i = preparedStatement.executeUpdate();
            // 遍历查询结果集
            System.out.println(i);
        } catch (Exception e) {
            logger.error("jdbc异常", e);
        } finally {
            closResource(null,preparedStatement, connection);
        }
    }

    @Test
    public void delete(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 通过驱动管理类获取数据库链接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=utf-8","root","root");
            // 定义sql语句？表示占位符persistence
            String sql = "delete from user where  id = ? and username = ?";
            // 获取预处理statement
            preparedStatement = connection.prepareStatement(sql);
            // 设置参数，第⼀个参数为sql语句中参数的序号(从1开始)，第⼆个参数为设置的参数值
            preparedStatement.setInt(1,5);
            preparedStatement.setString(2,"kitty");
            // 向数据库发出sql执⾏查询，查询出结果集
            int i = preparedStatement.executeUpdate();
            // 遍历查询结果集
            System.out.println(i);
        } catch (Exception e) {
            logger.error("jdbc异常", e);
        } finally {
            closResource(null,preparedStatement, connection);
        }
    }

    /**
     * 关闭资源
     */
    private void closResource(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
        // 释放资源
        if (resultSet != null) {
            try{
                resultSet.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (preparedStatement != null) {
            try{
                preparedStatement.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try{
                connection.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}
